﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Threading.Tasks;
using Utilities;

namespace DataAccess
{
    /// <summary>
    /// Database agnostic command
    /// </summary>
    /// The database command cannot be abstract since it needs to be created first to allow fluent transition to a more specific command (object, collection, etc)
    public class DatabaseCommand
    {
        public DatabaseCommand()
        {
            _Parameters = new List<DatabaseParameter>();
            _Type = CommandType.Text;
        }

        public int ReturnCode { get; set; }

        #region Internal properties

        /// <summary>
        /// The connection information
        /// </summary>
        internal DatabaseConnection _Connection { get; set; }

        /// <summary>
        /// The command type (defaults to stored proc)
        /// </summary>
        internal CommandType _Type { get; set; }

        /// <summary>
        /// The SQL statement to execute
        /// </summary>
        internal string _Sql { get; set; }

        /// <summary>
        /// The parameters to use
        /// </summary>
        internal IList<DatabaseParameter> _Parameters { get; set; }

        /// <summary>
        /// The timeout in seconds
        /// </summary>
        internal int? _Timeout { get; set; }

        /// <summary>
        /// The driver to perform database specific operations
        /// </summary>
        internal DatabaseDriver _Driver { get; set; }

        #endregion

        /// <summary>
        /// Executes a command
        /// </summary>
        /// <param name="context">The data access context</param>
        /// <returns></returns>
        public int Execute(DatabaseContext context = null)
        {
            try
            {
                if (context != null
                    && context.Connection != null) // It is coming from a transaction
                {
                    return ExecuteCommand(context.Transaction, context.Connection);
                }

                PreCondition.RequireNotNull(_Connection, "Connection");

                // If we are here that means that the command is not executing under a transaction
                DbProviderFactory providerFactory = DbProviderFactories.GetFactory(_Connection.ProviderName);

                using (DbConnection connection = providerFactory.CreateConnection())
                {
                    connection.ConnectionString = _Connection.ConnectionString;
                    connection.Open();

                    return ExecuteCommand(null, connection);
                }
            }
            catch (Exception exception)
            {
                Logger.DataAccess.LogError(exception);

                throw;
            }
        }

        internal async Task<int> ExecuteAsync(DatabaseContext context = null)
        {
            try
            {
                if (context != null
                    && context.Connection != null) // It is coming from a transaction
                {
                    return await ExecuteCommandAsync(context.Transaction, context.Connection);
                }

                PreCondition.RequireNotNull(_Connection, "Connection");

                // If we are here that means that the command is not executing under a transaction
                DbProviderFactory providerFactory = DbProviderFactories.GetFactory(_Connection.ProviderName);

                using (DbConnection connection = providerFactory.CreateConnection())
                {
                    connection.ConnectionString = _Connection.ConnectionString;
                    connection.Open();

                    return await ExecuteCommandAsync(null, connection);
                }
            }
            catch (Exception exception)
            {
                Logger.DataAccess.LogError(exception);

                throw;
            }
        }

        private int ExecuteCommand(DbTransaction transaction, DbConnection connection)
        {
            using (DbCommand command = connection.CreateCommand(_Type, _Sql, _Parameters, _Timeout, _Driver))
            {
                if (transaction != null)
                {
                    command.Transaction = transaction;
                }

                DbParameter returnParameter = null;

                bool useReturnValue = command.CommandType == CommandType.StoredProcedure;

                if (useReturnValue)
                {
                    // Create a parameter to store the return code
                    returnParameter = command.CreateParameter();
                    returnParameter.Direction = ParameterDirection.ReturnValue;
                    returnParameter.DbType = DbType.Int32;
                    command.Parameters.Add(returnParameter);
                }

                int rc = Execute(command);

                CopyOutParametersValue(command);

                if (useReturnValue)
                {
                    ReturnCode = returnParameter.Value != null ? (int)returnParameter.Value : 0;
                }

                return rc;
            }
        }

        private async Task<int> ExecuteCommandAsync(DbTransaction transaction, DbConnection connection)
        {
            using (DbCommand command = connection.CreateCommand(_Type, _Sql, _Parameters, _Timeout, _Driver))
            {
                if (transaction != null)
                {
                    command.Transaction = transaction;
                }

                DbParameter returnParameter = null;

                bool useReturnValue = command.CommandType == CommandType.StoredProcedure;

                if (useReturnValue)
                {
                    // Create a parameter to store the return code
                    returnParameter = command.CreateParameter();
                    returnParameter.Direction = ParameterDirection.ReturnValue;
                    returnParameter.DbType = DbType.Int32;
                    command.Parameters.Add(returnParameter);
                }

                int rc = await ExecuteAsync(command);

                CopyOutParametersValue(command);

                if (useReturnValue)
                {
                    ReturnCode = returnParameter.Value != null ? (int)returnParameter.Value : 0;
                }

                return rc;
            }
        }

        /// <summary>
        /// Copies the values of the parameters that are not input only to the database independent parameters of this command
        /// </summary>
        /// <param name="command"></param>
        private void CopyOutParametersValue(DbCommand command)
        {
            if (_Parameters == null)
            {
                return;
            }

            IDictionary<string, DatabaseParameter> parameters = _Parameters.ToDictionary(p => _Driver.ParameterPlaceHolder + p._Name);

            foreach (DbParameter parameter in command.Parameters)
            {
                if (parameter.Direction != ParameterDirection.Input
                    && parameter.Direction != ParameterDirection.ReturnValue)
                {
                    string name = parameter.ParameterName;
                    parameters[name]._Value = parameter.Value;
                }
            }
        }

        // The methods below are virtual and not abstract since we don't want this class to be abstract due to the fluent feature
        /// <summary>
        /// Executes a database command
        /// </summary>
        /// <param name="command">The command to be executed</param>
        /// <returns></returns>
        protected virtual int Execute(DbCommand command)
        {
            throw new NotImplementedException();
        }

        protected virtual Task<int> ExecuteAsync(DbCommand command)
        {
            throw new NotImplementedException();
        }

        public object ParameterValue(int index)
        {
            return _Parameters[index]._Value;
        }

        #region Fluent methods

        public DatabaseCommand Connection(string connectionName, DatabaseDriver driver = null)
        {
            _Connection = ConnectionManager.GetConnection(connectionName);

            if (driver != null)
            {
                _Driver = driver;
            }
            else
            {
                _Driver = DatabaseDriverManager.Drivers[_Connection.ProviderName];
            }

            return this;
        }

        public DatabaseCommand ConnectionString(string connectionString, string providerName, DatabaseDriver driver = null)
        {
            _Connection = new DatabaseConnection
            {
                ConnectionString = connectionString,
                ProviderName = providerName
            };

            if (driver != null)
            {
                _Driver = driver;
            }
            else
            {
                _Driver = DatabaseDriverManager.Drivers[providerName];
            }

            return this;
        }

        public DatabaseCommand Text(string sql)
        {
            _Sql = sql;
            _Type = CommandType.Text;

            return this;
        }

        // Functions are called using "SELECT * FROM Function(@p1, @p2, ...)"
        public DatabaseCommand StoredProcedure(string sql)
        {
            _Sql = sql;
            _Type = CommandType.StoredProcedure;

            return this;
        }

        public DatabaseCommand TableDirect(string sql)
        {
            _Sql = sql;
            _Type = CommandType.TableDirect;

            return this;
        }

        public DatabaseCommand Timeout(int timeout)
        {
            _Timeout = timeout;

            return this;
        }

        public DatabaseCommand Parameter(string name, object value, int? size = null)
        {
            return Parameter(new DatabaseParameter
            {
                _Name = name,
                _Value = value,
                _Size = size
            });
        }

        public DatabaseCommand Parameter(DatabaseParameter databaseParameter)
        {
            _Parameters.Add(databaseParameter);

            return this;
        }

        public DatabaseCommand Parameters(params DatabaseParameter[] databaseParameters)
        {
            foreach (DatabaseParameter databaseParameter in databaseParameters)
            {
                _Parameters.Add(databaseParameter);
            }

            return this;
        }

        /// <summary>
        /// Adds a table array parameter
        /// </summary>
        /// <typeparam name="I"></typeparam>
        /// <param name="typeName"></param>
        /// <param name="name"></param>
        /// <param name="collection"></param>
        /// <param name="columnName">The name of the column to supply if the parameter is a primitive</param>
        /// <returns></returns>
        public DatabaseCommand Parameter<I>(string typeName, string name, ICollection<I> collection, string columnName = null)
        {
            // Create the data table from the array
            DataTable table = CreateTable(typeName, collection, columnName);

            _Parameters.Add(new DatabaseParameter 
            { 
                _Name = name,
                _Type = (int)System.Data.SqlDbType.Structured,
                _Value = table
            });

            return this;
        }

        private DataTable CreateTable<I>(string typeName, ICollection<I> collection, string columnName)
        {
            DataTable table = new DataTable(typeName);
            Type itemType = typeof(I);

            if (itemType.IsPrimitive())
            {
                PreCondition.RequireNotNullOrEmpty(columnName, "columnName");

                table.Columns.Add(columnName);

                // Add the rows
                foreach (var item in collection)
                {
                    DataRow row = table.NewRow();
                    row[columnName] = item;
                    table.Rows.Add(row);
                }
            }
            else // Use reflection to create the table from the properties of the type
            {
                var accessor = itemType.GetTypeAccessor();

                // Add the columns
                foreach (var property in accessor.PropertyAccessors.Keys)
                {
                    table.Columns.Add(property);
                }

                // Add the rows
                foreach (var item in collection)
                {
                    DataRow row = table.NewRow();

                    foreach (var property in accessor.PropertyAccessors.Keys)
                    {
                        row[property] = accessor.GetValue(item, property);
                    }

                    table.Rows.Add(row);
                }
            }

            return table;
        }

        public DatabaseResponse<EmptyType> ExecuteNonQuery(DatabaseContext context = null)
        {
            NonQueryDatabaseCommand command = new NonQueryDatabaseCommand
            {
                _Connection = this._Connection,
                _Sql = this._Sql,
                _Type = this._Type,
                _Parameters = this._Parameters,
                _Timeout = this._Timeout,
                _Driver = this._Driver
            };

            command.Execute(context);

            return new DatabaseResponse<EmptyType>
            {
                AffectedRows = command.AffectedRows
            };
        }


        public async Task<DatabaseResponse<EmptyType>> ExecuteNonQueryAsync(DatabaseContext context = null)
        {
            NonQueryDatabaseCommand command = new NonQueryDatabaseCommand
            {
                _Connection = this._Connection,
                _Sql = this._Sql,
                _Type = this._Type,
                _Parameters = this._Parameters,
                _Timeout = this._Timeout,
                _Driver = this._Driver
            };

            await command.ExecuteAsync(context);

            return new DatabaseResponse<EmptyType>
            {
                AffectedRows = command.AffectedRows
            };
        }

        public ObjectDatabaseCommand<T> ToQueryObject<T>(
            Action<DbDataReader, T> onRecord = null, 
            TypeMap typeMap = null, 
            PropertyMap propertyMap = null)
        {
            return new ObjectDatabaseCommand<T>
            {
                _Connection = this._Connection,
                _Sql = this._Sql,
                _Type = this._Type,
                _Parameters = this._Parameters,
                _Timeout = this._Timeout,
                _Driver = this._Driver,
                OnRecord = onRecord,
                TypeMap = typeMap,
                PropertyMap = propertyMap
            };
        }

        public DatabaseResponse<T> QueryObject<T>(
            Action<DbDataReader, T> onRecord = null, 
            TypeMap typeMap = null, 
            PropertyMap propertyMap = null, 
            DatabaseContext context = null)
        {
            ObjectDatabaseCommand<T> command = ToQueryObject<T>(onRecord: onRecord, typeMap: typeMap, propertyMap: propertyMap);

            command.Execute(context);

            return new DatabaseResponse<T>
            {
                ReturnCode = command.ReturnCode,
                Data = command.Object
            };
        }

        public async Task<DatabaseResponse<T>> QueryObjectAsync<T>(
            Action<DbDataReader, T> onRecord = null,
            TypeMap typeMap = null,
            PropertyMap propertyMap = null,
            DatabaseContext context = null)
        {
            ObjectDatabaseCommand<T> command = ToQueryObject<T>(onRecord: onRecord, typeMap: typeMap, propertyMap: propertyMap);

            await command.ExecuteAsync(context);

            return new DatabaseResponse<T>
            {
                ReturnCode = command.ReturnCode,
                Data = command.Object
            };
        }

        public CollectionDatabaseCommand<T> ToQueryCollection<T>(
            Action<DbDataReader, T> onRecord = null, 
            TypeMap typeMap = null, 
            PropertyMap propertyMap = null)
        {
            return new CollectionDatabaseCommand<T>
            {
                _Connection = this._Connection,
                _Sql = this._Sql,
                _Type = this._Type,
                _Parameters = this._Parameters,
                _Timeout = this._Timeout,
                _Driver = this._Driver,
                OnRecord = onRecord,
                TypeMap = typeMap,
                PropertyMap = propertyMap
            };
        }

        public DatabaseResponse<IList<T>> QueryCollection<T>(
            Action<DbDataReader, T> onRecord = null, 
            TypeMap typeMap = null, 
            PropertyMap propertyMap = null, 
            DatabaseContext context = null)
        {
            CollectionDatabaseCommand<T> command = ToQueryCollection<T>(onRecord: onRecord, typeMap: typeMap, propertyMap: propertyMap);

            command.Execute(context);

            return new DatabaseResponse<IList<T>>
            {
                ReturnCode = command.ReturnCode,
                Data = command.Objects
            };
        }

        public async Task<DatabaseResponse<IList<T>>> QueryCollectionAsync<T>(
            Action<DbDataReader, T> onRecord = null,
            TypeMap typeMap = null,
            PropertyMap propertyMap = null,
            DatabaseContext context = null)
        {
            CollectionDatabaseCommand<T> command = ToQueryCollection<T>(onRecord: onRecord, typeMap: typeMap, propertyMap: propertyMap);

            await command.ExecuteAsync(context);

            return new DatabaseResponse<IList<T>>
            {
                ReturnCode = command.ReturnCode,
                Data = command.Objects
            };
        }

        public DatabaseResponse<T> ExecuteScalar<T>(DatabaseContext context = null)
        {
            ScalarDatabaseCommand<T> command = new ScalarDatabaseCommand<T>
            {
                _Connection = this._Connection,
                _Sql = this._Sql,
                _Type = this._Type,
                _Parameters = this._Parameters,
                _Timeout = this._Timeout,
                _Driver = this._Driver
            };

            command.Execute(context);

            return new DatabaseResponse<T>
            {
                Data = command.ReturnValue
            };
        }

        public async Task<DatabaseResponse<T>> ExecuteScalarAsync<T>(DatabaseContext context = null)
        {
            ScalarDatabaseCommand<T> command = new ScalarDatabaseCommand<T>
            {
                _Connection = this._Connection,
                _Sql = this._Sql,
                _Type = this._Type,
                _Parameters = this._Parameters,
                _Timeout = this._Timeout,
                _Driver = this._Driver
            };

            await command.ExecuteAsync(context);

            return new DatabaseResponse<T>
            {
                Data = command.ReturnValue
            };
        }

        public MultipleResultsCommand ToMultipleResults()
        {
            return new MultipleResultsCommand
            {
                _Connection = this._Connection,
                _Sql = this._Sql,
                _Type = this._Type,
                _Parameters = this._Parameters,
                _Timeout = this._Timeout,
                _Driver = this._Driver
            };
        }

        #endregion
    }
}
